#!/usr/bin/python3
import pymysql

# 打开数据库连接
db = pymysql.connect("www.xiezc.xyz", "root", "xzcbs1210", "daily", 8306)

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()


def findAllTag():
    query_sql = "select * from xs_tag "
    cursor.execute(query_sql)
    return cursor.fetchall()


def findTagId(name):
    query_sql = "select id from t_tag  where name = %s"
    cursor.execute(query_sql, name)
    return cursor.fetchone()


def findPhotoNotExist(album_id):
    query_photo_sql = "select id from t_photo where album_id = %s "
    cursor.execute(query_photo_sql, album_id)
    ids = []
    for row in cursor:
        ids.append(row[0])
    ret = []
    for id in ids:
        query_photo_sql = "select id from t_tag_photo where photo_id = %s "
        cursor.execute(query_photo_sql, id)
        if cursor.fetchone() is None:
            ret.append(id)
    return ret


def insertTagPhoto(ids, tag_id):
    insert_sql = "insert into t_tag_photo(tag_id,photo_id) values(%s,%s)"
    for id in ids:
        cursor.execute(insert_sql, (tag_id, id))
    db.commit()


try:
    flag = False
    data = findAllTag()
    tagDict = {}
    for row in data:
        print(row[0])
        if not flag:
            if row[0] == 144160:
                flag = True
                continue
            else:
                continue

        ids = findPhotoNotExist(row[1])
        if tagDict.get(row[2]) is None:
            tagId = findTagId(row[2])
            tagDict[row[2]] = tagId
        if len(ids) == 0:
            continue
        insertTagPhoto(ids, tagId)

except Exception as e:
    # 如果发生错误则回滚
    db.rollback()
    print(e)
finally:
    # 关闭数据库连接
    db.close()
